import crawler
import pymysql
import requests

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()

cursor.execute("drop database if exists testdb")
cursor.execute("CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4")
db.close()

db2 = pymysql.connect(host="localhost", user="root", password="123456", database="testdb", port=3306)
cursor2 = db2.cursor()
cursor2.execute("DROP TABLE IF EXISTS testtbl")

sql1 = """CREATE TABLE testtbl (name VARCHAR(255) NULL, type VARCHAR(255) NULL);"""
cursor2.execute(sql1)
print("Created table Successfull.")

class MysqlPipeline():
    def __init__(self,host,database,user,password,port):
        self.host=host
        self.database=database
        self.user=user
        self.password=password
        self.port=port
    def from_crawler(cls,crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            database=crawler.settings.get('MYSQL_DATABASE'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
            port=crawler.settings.get('MYSQL_PORT'),
        )
    def open_spider(self,spider):
        self.db=pymysql.connect(self.host,self.user,self.password,self.database,charset='utf-8',port=self.port)
        self.cursor=self.db.cursor()
        def close_spider(self,spider):
            self.db.close()
    def process_item(self,item,spider):
        data=dict(item)
        keys=','.json(data.keys())
        values=','.join(data.keys())
        values=','.join(['%s']*len(data))
        sql='insert into %s (%s) values (%s)'%(item.table,keys,values)
        self.cursor.execute(sql,tuple(data.values()))
        sql.db.commit()
        return item

    MYSQL_HOST = 'localhost'
    MYSQL_DATABASE = 'testdb'
    MYSQL_USER = 'root'
    MYSQL_PASSWORD = '123456'
    MYSQL_PORT = '3306'

